﻿using System;
using System.Configuration;
using System.Web.UI;
using System.Data;
using System.Data.SqlClient;


namespace mojoPortal.Web.RS.SBM.Controls
{
    public partial class StatusCounterControl : RSBaseUserControl
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                RSUserIdentity userIdentity = GetRSUserIdentity();
                LoadCallCount(userIdentity.RSCompanyId);
                LoadIssueCount(userIdentity.RSCompanyId);
                LoadWorkOrderCount(userIdentity.RSCompanyId);
                LoadEstimateCount(userIdentity.RSCompanyId);
            }
        }

        public void LoadCallCount(int companyid)
        {
            SqlCommand myCommand;
            SqlParameter param;
            SqlConnection myConnection;

            String sqlSELECT = "select COUNT(c.StatusId) as cnt, coalesce((select s.statusname from rs_tblStatus s where s.statusid=c.statusid), 'Undefined') as callstatus from rs_tblCall c where c.companyid=@companyid group by c.StatusId";

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
            myConnection.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            gvCallCount.DataSource = ds;
            gvCallCount.DataBind();
            myConnection.Close();
        }

        public void LoadIssueCount(int companyid)
        {
            SqlCommand myCommand;
            SqlParameter param;
            SqlConnection myConnection;

            String sqlSELECT = "select COUNT(i.issueid) as cnt, coalesce((select s.statusname from rs_tblStatus s where s.statusid=i.statusid), 'Undefined') as issuestatus from rs_tblissue i where i.companyid=@companyid group by i.statusid";

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
            myConnection.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            gvIssueCount.DataSource = ds;
            gvIssueCount.DataBind();
            myConnection.Close();
        }

        public void LoadEstimateCount(int companyid)
        {
            SqlCommand myCommand;
            SqlParameter param;
            SqlConnection myConnection;

            String sqlSELECT = "select count(p.EstimateId) as cnt, coalesce((select s.statusname from rs_tblStatus s where s.statusid=p.Estimatestatusid), 'Undefined') as Estimatestatus from rs_tblEstimate p where p.companyid=@companyid group by p.Estimatestatusid";

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
            myConnection.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            gvEstimateCount.DataSource = ds;
            gvEstimateCount.DataBind();
            myConnection.Close();
        }


        public void LoadWorkOrderCount(int companyid)
        {
            SqlCommand myCommand;
            SqlParameter param;
            SqlConnection myConnection;

            String sqlSELECT = "select count(p.WorkOrderId) as cnt, coalesce((select s.statusname from rs_tblStatus s where s.statusid=p.WorkOrderstatusid), 'Undefined') as WorkOrderstatus from rs_tblWorkOrder p where p.companyid=@companyid group by p.WorkOrderstatusid";

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
            myConnection.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            gvWorkOrderCount.DataSource = ds;
            gvWorkOrderCount.DataBind();
            myConnection.Close();
        }

        
        
    }
}